package com.alks.common.utils.DatabaseUtils;

import com.alks.common.utils.beanutils.ArrayUtils;
import com.alks.entity.data.response.ResponseInfo;
import com.alks.entity.exception.ServiceErrorException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.jetbrains.annotations.Nullable;

import java.sql.*;
import java.time.YearMonth;
import java.time.format.DateTimeFormatter;
import java.util.List;
import java.util.regex.Pattern;

import static com.alks.entity.data.enums.ResultCodeEnum.INPUT_ERROR;

@Slf4j
public class DatabaseUtils {
    // 定义数据库连接信息
    private static final String DB_URL = "jdbc:p6spy:mysql://192.168.1.220/MES";
    private static final String USER = "root";
    private static final String PASS = "root";

    // 获取数据库连接方法
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(DB_URL, USER, PASS);
    }

    // 执行查询方法
    public static ResultSet executeQuery(Connection conn, String sql) throws SQLException {
        Statement stmt = conn.createStatement();
        return stmt.executeQuery(sql);
    }

    // 执行写入操作方法
    public static void executeInsert(Connection conn, String sql, Object... params) throws SQLException {
        PreparedStatement pstmt = conn.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            pstmt.setObject(i + 1, params[i]);
        }
        pstmt.executeUpdate();
    }


    /**
     * 结算时间核验 过去一年到当前时间可以结算
     *
     * @param financeYymm
     * @return
     */
    public static @Nullable ResponseInfo<Object> financeDateCheck(String financeYymm) {
        YearMonth financeRec = YearMonth.parse(financeYymm, DateTimeFormatter.ofPattern("yyyyMM"));
        //早于当前一年或者晚于当前时间不能检核
        if (financeRec.isBefore(YearMonth.now().minusYears(1)) || financeRec.isAfter(YearMonth.now())) {
            StackTraceElement[] stackTraceElements = Thread.currentThread().getStackTrace();
            String callingMethodName = stackTraceElements[2].getMethodName();
            log.warn("异常接口：{}\n异常原因：早于当前一年或者晚于当前时间不能结算\n当前输入检核时间：{}\n", callingMethodName, financeYymm);
            return ResponseInfo.error("结算月份有误", INPUT_ERROR.getCode());
        }
        return null;
    }

    /**
     * SQL注入检测(在SQL中搭配``使用)
     *
     * @param sql 要检测的SQL语句
     */
    public static void sqlCheck(String sql) {
        if (StringUtils.isBlank(sql)){
            return;
        }
        // 检测注释符 -- 或 /* ... */
        boolean check1 = sql.contains("--");
        boolean check2 = Pattern.compile("/\\*.*?\\*/").matcher(sql).find();
        boolean check3 = sql.contains("`");
        boolean check4 = sql.contains(";");
        boolean check5 = sql.contains(" ");
        boolean check = (check1 || check2) && check3 && check4 && check5;
        if (check) {
            throw new ServiceErrorException("?");
        }
    }

    /**
     * SQL注入检测(在SQL中搭配``使用)
     *
     * @param sqlList 要检测的SQL语句
     */
    public static void sqlCheck(List<String> sqlList) {
        if (ArrayUtils.isEmpyt(sqlList)){
            return;
        }
        for (String sql : sqlList) {
            // 检测注释符 -- 或 /* ... */
            sqlCheck(sql);
        }
    }
}
